package com.linlang.grasharepro.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.linlang.grasharepro.model.Follow;
import com.linlang.grasharepro.model.User;
import com.linlang.grasharepro.utils.DataBaseOpenHelper;

import java.util.ArrayList;

/**
 * Created by lenovo on 2016/8/2.
 */
public class UserDB {
    public static final String SQLITE_TABLE = "user";

    public static final String PK = "_id";
    public static final String LOGIN_NAME = "loginName";
    public static final String LOGIN_PASS = "loginPass";
    public static final String DISPLAY_NAME = "displayName";
    public static final String SEX = "sex";
    public static final String BIRTHDAY = "birthday";
    public static final String MAJOR = "major";
    public static final String ROLE = "role";
    public static final String SCHOOL = "school";
    public static final String ACADEMIC_STARTS = "academicStarts";
    public static final String GRADUATION_TIME = "graduationTime";
    public static final String WORK = "work";
    public static final String LACATION = "location";
    public static final String CONTACTWAY = "contactWay";
    public static final String MAIL = "mail";
    public static final String INTRODUCTION = "introduction";
    public static final String HEAD_URL = "headUrl";
    public static final String REAL_NAME = "realName";


    private static final String TAG = "UserDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase sqLiteDatabase;
    private final Context context;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {

            super(context, DataBaseOpenHelper.DATABASE_NAME, null, DataBaseOpenHelper.DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
            onCreate(db);
        }
    }

    public UserDB(Context context) {
        this.context = context;
    }

    public UserDB open() throws SQLException {
        mDbHelper = new DatabaseHelper(context);
        sqLiteDatabase = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        if (mDbHelper != null) {
            mDbHelper.close();
        }
    }

    /**
     * 新增数据
     *
     * @param user
     * @return
     */
    public int insertUser(User user) {
        long createResult = 0;
        int strid = 0;
        ContentValues initialValues = new ContentValues();
        initialValues.put(LOGIN_NAME, user.getLoginName());
        initialValues.put(LOGIN_PASS, user.getLoginPass());
        initialValues.put(MAJOR, user.getMajor());
        initialValues.put(ROLE, user.getRole());
        initialValues.put(SCHOOL, user.getSchool());
        initialValues.put(ACADEMIC_STARTS, user.getAcademicStarts());
        initialValues.put(GRADUATION_TIME, user.getGraduationTime());
        initialValues.put(HEAD_URL, user.getHeadUrl());
        initialValues.put(DISPLAY_NAME, user.getDisplayName());
        initialValues.put(SEX, user.getSex());
        initialValues.put(BIRTHDAY, user.getBirthday());
        initialValues.put(WORK, user.getWork());
        initialValues.put(LACATION, user.getLocation());
        initialValues.put(CONTACTWAY, user.getContactWay());
        initialValues.put(MAIL, user.getMail());
        initialValues.put(INTRODUCTION, user.getIntroduction());
        initialValues.put(REAL_NAME, user.getRealName());
        try {
            createResult = sqLiteDatabase.insert(SQLITE_TABLE, null, initialValues);

            Cursor cursor = sqLiteDatabase.rawQuery("select last_insert_rowid() from " + SQLITE_TABLE, null);

            if (cursor.moveToFirst())
                strid = cursor.getInt(0);

            System.out.println("id:" + strid);
            System.out.println("================================================================================");

        } catch (Exception e) {
            // TODO: handle exception
        }
        return strid;
    }

    /**
     * 登陆验证
     *
     * @param user
     * @return
     */
    public ArrayList<User> login(User user) {

        ArrayList<User> allTicketsList = new ArrayList<User>();
        Cursor mCursor = null;
        mCursor = sqLiteDatabase.query(SQLITE_TABLE, null, LOGIN_NAME + "=? and " + LOGIN_PASS + "=?", new String[]{user.getLoginName(), user.getLoginPass()}, null, null, null);
        if (mCursor.moveToFirst()) {
            do {
                User userInfo = new User();
                userInfo.set_Id(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(PK)));
                userInfo.setLoginName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_NAME)));
                userInfo.setLoginPass(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_PASS)));
                userInfo.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                userInfo.setRole(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ROLE)));
                userInfo.setSchool(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SCHOOL)));
                userInfo.setAcademicStarts(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ACADEMIC_STARTS)));
                userInfo.setGraduationTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(GRADUATION_TIME)));
                userInfo.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));
                userInfo.setIntroduction(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(INTRODUCTION)));
                userInfo.setRealName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(REAL_NAME)));
                allTicketsList.add(userInfo);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return allTicketsList;
    }

    /**
     * 删除表的全部字段数据
     *
     * @return
     */
    public boolean deleteAllUsers() {
        int doneDelete = 0;
        try {
            doneDelete = sqLiteDatabase.delete(SQLITE_TABLE, null, null);
            Log.w(TAG, Integer.toString(doneDelete));
            Log.e("doneDelete", doneDelete + "");
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return doneDelete > 0;
    }

    /**
     * 获取表中的所有字段
     *
     * @return
     */
    public User getUser(String loginName) {

        Cursor mCursor = null;
        mCursor = sqLiteDatabase.query(SQLITE_TABLE, null, LOGIN_NAME + "=?", new String[]{loginName}, null, null, null);
        User user = new User();
        if (mCursor.moveToFirst()) {
            do {
                user.set_Id(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(PK)));
                user.setLoginName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_NAME)));
                user.setLoginPass(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_PASS)));
                user.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                user.setRole(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ROLE)));
                user.setSchool(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SCHOOL)));
                user.setAcademicStarts(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ACADEMIC_STARTS)));
                user.setGraduationTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(GRADUATION_TIME)));
                user.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));

                user.setSex(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SEX)));
                user.setDisplayName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(DISPLAY_NAME)));
                user.setBirthday(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(BIRTHDAY)));
                user.setWork(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(WORK)));
                user.setLocation(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LACATION)));
                user.setContactWay(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTACTWAY)));
                user.setMail(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAIL)));
                user.setIntroduction(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(INTRODUCTION)));
                user.setRealName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(REAL_NAME)));
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return user;
    }

    /**
     * 获取表中的所有字段
     *
     * @return
     */
    public int updateUser(User user) {

        ContentValues initialValues = new ContentValues();
        initialValues.put(MAJOR, user.getMajor());
        initialValues.put(ROLE, user.getRole());
        initialValues.put(SCHOOL, user.getSchool());
        initialValues.put(ACADEMIC_STARTS, user.getAcademicStarts());
        initialValues.put(GRADUATION_TIME, user.getGraduationTime());
        initialValues.put(HEAD_URL, user.getHeadUrl());
        initialValues.put(DISPLAY_NAME, user.getDisplayName());
        initialValues.put(SEX, user.getSex());
        initialValues.put(BIRTHDAY, user.getBirthday());
        initialValues.put(WORK, user.getWork());
        initialValues.put(LACATION, user.getLocation());
        initialValues.put(CONTACTWAY, user.getContactWay());
        initialValues.put(MAIL, user.getMail());
        initialValues.put(INTRODUCTION, user.getIntroduction());
        initialValues.put(REAL_NAME, user.getRealName());

        int updateIndex = sqLiteDatabase.update(SQLITE_TABLE, initialValues, LOGIN_NAME + "=?", new String[]{user.getLoginName()});
        return updateIndex;
    }


    /**
     * @param id
     * @return
     */
    public boolean deleteUserById(String id) {
        int isDelete;
        String[] ids;
        ids = new String[]{id};
        isDelete = sqLiteDatabase.delete(SQLITE_TABLE, PK + "=?", ids);
        Log.e("deleteTicket", "isDelete:" + isDelete + "||" + "ticketID="
                + id);
        return isDelete > 0;
    }

    public void insertSomeTickets() {

    }


    /**
     * 获取表中的所有字段
     *
     * @return
     */
    public ArrayList<User> fetchAll() {

        ArrayList<User> allTicketsList = new ArrayList<User>();
        Cursor mCursor = null;
        mCursor = sqLiteDatabase.query(SQLITE_TABLE, null, null, null, null, null, null);
        if (mCursor.moveToFirst()) {
            do {
                User user = new User();
                user.set_Id(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(PK)));
                user.setLoginName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_NAME)));
                user.setLoginPass(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_PASS)));
                user.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                user.setRole(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ROLE)));
                user.setSchool(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SCHOOL)));
                user.setAcademicStarts(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ACADEMIC_STARTS)));
                user.setGraduationTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(GRADUATION_TIME)));
                user.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));

                user.setSex(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SEX)));
                user.setDisplayName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(DISPLAY_NAME)));
                user.setBirthday(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(BIRTHDAY)));
                user.setWork(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(WORK)));
                user.setLocation(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LACATION)));
                user.setContactWay(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTACTWAY)));
                user.setMail(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAIL)));
                user.setIntroduction(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(INTRODUCTION)));
                user.setRealName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(REAL_NAME)));
                allTicketsList.add(user);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return allTicketsList;
    }

    /**
     * 查询我的粉丝
     *
     * @return
     */
    public ArrayList<User> queryMyFans(String userName) {

        String sql = "select t1.* from "
                + SQLITE_TABLE + " t1, "
                + FansDB.SQLITE_TABLE + " t2 "
                + " where t1." + PK + "=t2." + FansDB.RELATION_ID
                + " and t2." + FansDB.USER_NAME + "='" + userName + "'";


        ArrayList<User> users = new ArrayList<User>();
        Cursor mCursor = null;
//        mCursor = sqLiteDatabase.query(SQLITE_TABLE, new String[]{PK, LOGIN_NAME,
//                LOGIN_PASS, MAJOR, ROLE, SCHOOL, ACADEMIC_STARTS, GRADUATION_TIME, HEAD_URL}, null, null, null, null, null);
        mCursor = sqLiteDatabase.rawQuery(sql, null);
        if (mCursor.moveToFirst()) {
            do {
                User user = new User();
                user.set_Id(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(PK)));
                user.setLoginName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_NAME)));
                user.setLoginPass(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_PASS)));
                user.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                user.setRole(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ROLE)));
                user.setSchool(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SCHOOL)));
                user.setAcademicStarts(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ACADEMIC_STARTS)));
                user.setGraduationTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(GRADUATION_TIME)));
                user.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));

                user.setSex(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SEX)));
                user.setDisplayName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(DISPLAY_NAME)));
                user.setBirthday(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(BIRTHDAY)));
                user.setWork(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(WORK)));
                user.setLocation(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LACATION)));
                user.setContactWay(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTACTWAY)));
                user.setMail(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAIL)));
                user.setIntroduction(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(INTRODUCTION)));
                user.setRealName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(REAL_NAME)));
                users.add(user);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return users;
    }

    /**
     * 查询我关注的人
     *
     * @return
     */
    public ArrayList<User> queryMyFollow (String userName) {

        String sql = "select t1.* from "
                + SQLITE_TABLE + " t1, "
                + FollowDB.SQLITE_TABLE + " t2 "
                + " where t1." + PK + "=t2." + FollowDB.RELATION_ID
                + " and t2." + FollowDB.USER_NAME + "='" + userName + "'";


        ArrayList<User> users = new ArrayList<User>();
        Cursor mCursor = null;
//        mCursor = sqLiteDatabase.query(SQLITE_TABLE, new String[]{PK, LOGIN_NAME,
//                LOGIN_PASS, MAJOR, ROLE, SCHOOL, ACADEMIC_STARTS, GRADUATION_TIME, HEAD_URL}, null, null, null, null, null);
        mCursor = sqLiteDatabase.rawQuery(sql, null);
        if (mCursor.moveToFirst()) {
            do {
                User user = new User();
                user.set_Id(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(PK)));
                user.setLoginName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_NAME)));
                user.setLoginPass(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LOGIN_PASS)));
                user.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                user.setRole(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ROLE)));
                user.setSchool(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SCHOOL)));
                user.setAcademicStarts(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(ACADEMIC_STARTS)));
                user.setGraduationTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(GRADUATION_TIME)));
                user.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));

                user.setSex(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SEX)));
                user.setDisplayName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(DISPLAY_NAME)));
                user.setBirthday(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(BIRTHDAY)));
                user.setWork(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(WORK)));
                user.setLocation(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(LACATION)));
                user.setContactWay(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTACTWAY)));
                user.setMail(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAIL)));
                user.setIntroduction(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(INTRODUCTION)));
                user.setRealName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(REAL_NAME)));
                users.add(user);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return users;
    }

    public void updateUserHead(User user){
        ContentValues initialValues = new ContentValues();
        initialValues.put(HEAD_URL, user.getHeadUrl());

        int updateIndex = sqLiteDatabase.update(SQLITE_TABLE, initialValues, PK + "=?", new String[]{user.get_Id()});
    }
}
